<?php

namespace App\Libs;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use App\Models\BankType;
use App\Models\BankContrast;
use App\Models\City;

class ExcelLibrary
{
     

    /**
     * 导出
     */
    public function export(array $titles, array $datas, array $fields, string $file_name)
    {
        //让程序一直运行
        set_time_limit(600);
        //设置程序运行内存
        ini_set('memory_limit', '300M');

        $count = count($titles)-1;//计算所需表头数量
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //设置列
        $rowAr=self::setRowAr($count);
        foreach ($rowAr as $k => $v) {
            if($k>$count) break;
            $sheet->setCellValue($v.'1',$titles[$k]);
        }

        //写入值
        foreach ($datas as $k => $v) {
            foreach ($rowAr as $ke => $ve){
                if($ke>$count) break;
                $sheet->setCellValue($ve.($k+2),$v[$fields[$ke]]);
                $spreadsheet->getActiveSheet()->getColumnDimension($ve)->setWidth(10); //固定列宽
            }
        }
        //冻结首行
        $spreadsheet->getActiveSheet()->freezePaneByColumnAndRow(0,2);

        //在输出Excel前，缓冲区中处理BOM头
        ob_end_clean();
        ob_start();

        $fileNameArr = explode('.',$file_name);
        $ext = array_pop($fileNameArr);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=$file_name");
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($spreadsheet, ucfirst($ext));//把后缀名首字母大写
        $objWriter->save('php://output');
        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

   
    public static function setRowAr($count=26)
    {
        $indData='A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z';
        $indData=explode(',',$indData);
        $curCount=26;
        for ($i=0; $i <26 ; $i++) {
            for ($j=0; $j <26 ; $j++) {
                if($curCount>=$count) return $indData;
                $indData[]=$indData[$i].$indData[$j];
                $curCount++;
            }
        }
        return $indData;
    }

    
    /**
     * 导入Excel到数据表
     *
     * @return mixed
     */
    public function import($filePath)
    {
        ini_set ("memory_limit","-1");
        //$reader = IOFactory::createReader('Xls');
         $reader = IOFactory::createReader('Xlsx');
         //载入excel文件
         $excel = $reader->load($filePath);
         //读取第一张表
         $sheet = $excel->getSheet(0);
         //获取总行数
         $row_num = $sheet->getHighestRow();
         //获取总列数
         $col_num = $sheet->getHighestColumn();
 
         $import_data = []; //数组形式获取表格数据
         for ($i = 2; $i <= $row_num; $i++) {
             $code = trim($excel->getActiveSheet()->getCell("A" . $i)->getValue());
             $str = str_replace('，',',',$excel->getActiveSheet()->getCell("B" . $i)->getValue());
             $arr = array_values(array_filter(explode(',',$str)));
             $arr_data = [];
             foreach($arr as $k=>$v){
                 $v = trim($v);
                 
             } 
        
         } 

    }

   

}
